package top.hcode.hoj.collstat;

import lombok.SneakyThrows;
import org.nutz.lang.Files;
import org.nutz.lang.Times;
import org.nutz.lang.util.NutMap;
import org.sagacity.sqltoy.model.MapKit;
import top.hcode.hoj.BaseDbApp;
import top.hcode.hoj.NumParseApp;
import top.hcode.hoj.common.LazyDao;
import top.hcode.hoj.pojo.entity.org.Org;
import top.hcode.hoj.utils.SqlUtil;

import java.io.*;
import java.text.DecimalFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

/**
 * 统计各学校的成绩情况
 * :author orangej
 * :since 2025/3/4
 */
public class CollStatSchoolApp extends BaseDbApp {

    // 添加静态参数类
    private static class StatParam {
        String collId;          // 活动ID
        Date startTime;         // 开始时间
        Date endTime;          // 结束时间
        Integer highScore;      // 优秀分数线
        String outputPath;      // 输出路径
        String name;           // 参数名称，用于注释标识

        public StatParam(String name, String collId, String startTime, String endTime, 
                        Integer highScore, String outputPath) {
            this.name = name;
            this.collId = collId;
            this.startTime = Times.D(startTime);
            this.endTime = Times.D(endTime);
            this.highScore = highScore;
            this.outputPath = outputPath;
        }
    }

    // 定义参数实例
    private static final StatParam PRIMARY_PARAM = new StatParam(
            "小学",
            "1895762323952381954",
            "2025-3-1",
            "2025-3-7 23:59:59",
            55,
            "d:\\1124\\小学\\"
    );

    private static final StatParam MIDDLE_PARAM = new StatParam(
            "中学",
            "1895783965273370625",
            "2025-3-1",
            "2025-3-7 23:59:59",
            65,
            "d:\\1124\\中学\\"
    );

    // 在这里切换参数
//    private static final StatParam CURRENT_PARAM = MIDDLE_PARAM;
    private static final StatParam CURRENT_PARAM = PRIMARY_PARAM;

    private static LazyDao dao = buildLazyDao();

    public static void main(String[] args) {
        Files.makeDir(new File(CURRENT_PARAM.outputPath));

        List<SchoolStatReportGenerator.ExamStats> allSchoolStats = new ArrayList<>();

        Map<String, Object> globalParams = MapKit.startOf("collId", CURRENT_PARAM.collId)
                .of("startDate", CURRENT_PARAM.startTime)
                .endOf("endDate", CURRENT_PARAM.endTime);
        List<Org> campusList = dao.findBySql(SqlUtil.sql("select * from org where id in \n" +
                                                         "(select DISTINCT campus_id from org_class where id in (select DISTINCT class_id from coll_user_stat where coll_id = :collId))"),
                globalParams, Org.class);

        // campus.id -> school
        Map<String, Org> schoolMap = new HashMap<>();
        campusList.parallelStream().forEach(campus -> schoolMap.put(campus.getId(),
                dao.selectOne(Org.class, "select * from org where id = ?", campus.getParentId())));

        String filter = "#[and colL_id = :collId]" +
                        "#[and start_answer_at > :startDate]" +
                        "#[and start_answer_at < :endDate]" +
                        "#[and class_id in (select id from org_class where campus_id = :campusId)]";
        globalParams.put("filter", filter);

        campusList.parallelStream().forEach(campus -> {
            String campusName = campus.getOrgName();
            Org school = schoolMap.get(campus.getId());
            String schoolName = school.getOrgName();

            Map<String, Object> params = new HashMap<>(globalParams);
            params.put("campusId", campus.getId());


            // 参与人数
            int totalNum = dao.fetchInt(SqlUtil.fmt("select count(1) from coll_user_stat where %s", filter), params);

            // 交卷人数
            int submitNum = dao.fetchInt(SqlUtil.fmt("select count(1) from coll_user_stat where  %s and submit_at is not null;", filter), params);

            // 学校数量
//            int schoolNum = dao.fetchInt(fmt("select count(distinct t1.org_id) from (select user_id from coll_user_stat where %s) t0\n" +
//                                             "left join school_has_user t1 on t0.user_id = t1.user_id", filter), params);

            // 班级数量
            int classNum = dao.fetchInt(SqlUtil.fmt("select count(DISTINCT class_id) from coll_user_stat where %s", filter), params);

            // 参与班级前10
            List<NutMap> top10Class = dao.queryMap(SqlUtil.fmt("select t1.org_name, cnt from \n" +
                                                               "(select class_id, count(*) cnt from coll_user_stat where %s group by class_id order by cnt desc limit 10) t0\n" +
                                                               "left join org t1 on t0.class_id = t1.id\n", filter), params);

            // 得分50名
            List<NutMap> top20Score = dao.queryMap(SqlUtil.fmt("select t3.org_name schoolName, t2.org_name className, real_name, objective_score, submit_at, minutes from \n" +
                                                               "(\n" +
                                                               "select user_id, objective_score, submit_at, TIMESTAMPDIFF(Minute,start_answer_at,submit_at) minutes from coll_user_stat " +
                                                               "where %s and TIMESTAMPDIFF(Minute,start_answer_at,submit_at) > 20 order by objective_score desc limit 50) t0\n" +
                                                               "left join class_has_user t1 on t0.user_id = t1.user_id\n" +
                                                               "left join org t2 on t1.org_id = t2.id -- class\n" +
                                                               "left join org t3 on t3.tree_code = SUBSTR(t2.tree_code, 1, 4)\t\t\n" +
                                                               "left join org_user t4 on t0.user_id = t4.id;", filter), params);

            // 选择题得分
            List<Integer> scoreList = dao.queryInt(SqlUtil.fmt("select objective_score from coll_user_stat where %s and submit_at is not null;", filter), params);
            NumParseApp.NumParseResult scoreResult = NumParseApp.analyze(scoreList);

            // 交卷时间
            List<Integer> timeList = dao.queryInt(SqlUtil.fmt("select TIMESTAMPDIFF(Minute,start_answer_at,submit_at) from coll_user_stat where %s and submit_at is not null;", filter), params);
            NumParseApp.NumParseResult timeResult = NumParseApp.analyze(timeList);

            SchoolStatReportGenerator.ExamStats stats = new SchoolStatReportGenerator.ExamStats();
            stats.totalStudents = totalNum;
            stats.submittedStudents = submitNum;
            stats.schoolName = schoolName;
            stats.campusName = campusName;
            stats.totalClasses = classNum;
            stats.topClasses = top10Class.stream().map(cls -> new SchoolStatReportGenerator.ClassInfo(cls.getString("orgName"), cls.getInt("cnt"))).collect(Collectors.toList());

            stats.minScore = scoreResult.min;
            stats.maxScore = scoreResult.max;
            stats.avgScore = scoreResult.average;
            stats.highScore = CURRENT_PARAM.highScore;
            stats.highScoreRate = scoreList.stream().filter(score -> score >= CURRENT_PARAM.highScore).count() / (double) scoreList.size();
            stats.scoreDistribution = new TreeMap<>();
            for (NumParseApp.RegionPercent regionPercent : scoreResult.regionPercentList) {
                int start = regionPercent.min;
                int end = regionPercent.max;
                double percent = regionPercent.percent;
                stats.scoreDistribution.put(String.format("%d-%d", start, end), percent);
            }

            stats.minTime = timeResult.min;
            stats.maxTime = timeResult.max;
            stats.avgTime = timeResult.average;
            stats.timeDistribution = new TreeMap<>();
            for (NumParseApp.RegionPercent regionPercent : timeResult.regionPercentList) {
                int start = regionPercent.min;
                int end = regionPercent.max;
                double percent = regionPercent.percent;
                stats.timeDistribution.put(String.format("%d-%d", start, end), percent);
            }

            AtomicInteger index = new AtomicInteger(1);
            stats.topStudents = top20Score.stream().map(score -> {
                        SchoolStatReportGenerator.StudentRank rank = new SchoolStatReportGenerator.StudentRank();
                        rank.rank = index.getAndIncrement();
                        rank.className = score.getString("className");
                        rank.name = score.getString("realName");
                        rank.score = score.getInt("objectiveScore");
                        rank.submitTime = score.getAs("submitAt", Date.class);
                        rank.duration = score.getInt("minutes");
                        return rank;
                    }
            ).collect(Collectors.toList());

            String report = SchoolStatReportGenerator.generateReport(stats);
            String fileSchoolName = schoolName;
            if (!campusName.equals(schoolName)) {
                fileSchoolName = schoolName + "(" + campusName + ")";
            }
            String filename = "调研统计报告-" + fileSchoolName + ".html";
            String fullPath = CURRENT_PARAM.outputPath + File.separator + filename;

            try {
                Files.write(new File(fullPath), report.getBytes("UTF-8"));
            } catch (UnsupportedEncodingException e) {
                throw new RuntimeException(e);
            }

            allSchoolStats.add(stats);
        });

        writeStatsToCsv(allSchoolStats, CURRENT_PARAM.outputPath);
    }

    @SneakyThrows
    private static void writeStatsToCsv(List<SchoolStatReportGenerator.ExamStats> statsList, String outputPath) {
        String csvFileName = outputPath + File.separator + "学校统计数据.csv";
        StringBuilder builder = new StringBuilder();
        builder.append("学校名称,校区名称,参与人数,交卷人数,班级数量,最低分,最高分,平均分,高分率,最短用时(分钟),最长用时(分钟),平均用时(分钟)\n");

        DecimalFormat df = new DecimalFormat("0.00");

        for (SchoolStatReportGenerator.ExamStats stats : statsList) {
            StringBuilder csvLine = new StringBuilder();
            csvLine.append(stats.schoolName).append(",")
                    .append(stats.campusName).append(",")
                    .append(stats.totalStudents).append(",")
                    .append(stats.submittedStudents).append(",")
                    .append(stats.totalClasses).append(",")
                    .append(stats.minScore).append(",")
                    .append(stats.maxScore).append(",")
                    .append(df.format(stats.avgScore)).append(",")
                    .append(df.format(stats.highScoreRate * 100)).append("%,")
                    .append(stats.minTime).append(",")
                    .append(stats.maxTime).append(",")
                    .append(df.format(stats.avgTime)).append("\n");

            builder.append(csvLine);

        }

        FileOutputStream fileOutputStream = new FileOutputStream(csvFileName);
        Writer writer = new BufferedWriter(new OutputStreamWriter(fileOutputStream, "UTF-8"));
        try {
            fileOutputStream.write(new byte[]{(byte)0xEF, (byte)0xBB, (byte)0xBF});
            writer.write(builder.toString());
        } finally {
            writer.close();
        }
    }
}
